<!DOCTYPE html>
<html>

<head>
  <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
  <meta name="robots" content="noindex" />
  <title>excelJS + x-spreadsheet 导入导出 Demo</title>
  <style>
    #drop {
      border: 2px dashed #bbb;
      -moz-border-radius: 5px;
      -webkit-border-radius: 5px;
      border-radius: 5px;
      padding: 25px;
      text-align: center;
      font: 20pt bold, "Vollkorn";
      color: #bbb
    }

    #b64data {
      width: 100%;
    }

    a {
      text-decoration: none
    }
  </style>
  <!-- x-spreadsheet stylesheet -->
  <link rel="stylesheet" href="https://unpkg.com/x-data-spreadsheet/dist/xspreadsheet.css" />
</head>

<body>
  <pre>
<b><a href="https://github.com/changgeee/x-spreadsheet-import-export">ExcelJS + x-spreadsheet 导入导出 Demo</a></b>

<div id="drop">Drop a xlsx file here to see sheet data</div>
<input type="file" name="xlfile" id="xlf" /> ... or click here to select a file
<textarea id="b64data">... or paste a base64-encoding here</textarea>
</pre>
  <p><input type="submit" value="Export to XLSX!" id="xport" onclick="export_xlsx();"></p>
  <div id="htmlout"></div>
  <br />
  <script src="https://unpkg.com/x-data-spreadsheet/dist/xspreadsheet.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/lodash.js/4.17.21/lodash.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/babel-polyfill/6.26.0/polyfill.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/exceljs/4.4.0/exceljs.min.js"></script>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/tinycolor/1.6.0/tinycolor.min.js"></script>
  <script>


    var HTMLOUT = document.getElementById('htmlout');
    var xspr = x_spreadsheet(HTMLOUT);
    HTMLOUT.style.height = (window.innerHeight - 400) + "px";
    HTMLOUT.style.width = (window.innerWidth - 50) + "px";

    var argb2hex = function (val) {
      val = val.trim().toLowerCase();
      var color = {};
      try {
        var argb = /^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(val)
        color.r = parseInt(argb[2], 16);
        color.g = parseInt(argb[3], 16);
        color.b = parseInt(argb[4], 16);
        color.a = parseInt(argb[1], 16) / 255;
        return tinycolor(`rgba(${color.r}, ${color.g}, ${color.b}, ${color.a})`).toHexString()
      } catch (e) {
        console.log(e)
      }
    }

    var hex2argb = function (val) {
      var rgb = tinycolor(val).toRgb()
      var rHex = parseInt(rgb.r).toString(16).padStart(2, '0')
      var gHex = parseInt(rgb.g).toString(16).padStart(2, '0')
      var bHex = parseInt(rgb.b).toString(16).padStart(2, '0')
      var aHex = parseInt(rgb.a).toString(16).padStart(2, '0')
      var res = aHex + rHex + gHex + bHex
      return res;
    }

    var e2x = function (wb) {
      let workbookData = [];
      wb.eachSheet((sheet, sheetIndex) => {
        // 构造x-data-spreadsheet 的 sheet 数据源结构
        let sheetData = { name: sheet.name, styles: [], rows: {}, merges: [] }
        // 收集合并单元格信息
        let mergeAddressData = []
        for (let mergeRange in sheet._merges) {
          sheetData.merges.push(sheet._merges[mergeRange].shortRange)
          let mergeAddress = {}
          // 合并单元格起始地址
          mergeAddress.startAddress = sheet._merges[mergeRange].tl
          // 合并单元格终止地址
          mergeAddress.endAddress = sheet._merges[mergeRange].br
          // Y轴方向跨度
          mergeAddress.YRange = sheet._merges[mergeRange].model.bottom - sheet._merges[mergeRange].model.top
          // X轴方向跨度
          mergeAddress.XRange = sheet._merges[mergeRange].model.right - sheet._merges[mergeRange].model.left
          mergeAddressData.push(mergeAddress)
        }
        sheetData.cols = {}
        for (let i = 0; i < sheet.columns.length; i++) {
          sheetData.cols[i.toString()] = {}
          if (sheet.columns[i].width) {
            sheetData.cols[i.toString()].width = sheet.columns[i].width * 8
          } else {
            // 默认列宽
            sheetData.cols[i.toString()].width = 100
          }
        }

        // 遍历行
        sheet.eachRow((row, rowIndex) => {
          sheetData.rows[(rowIndex - 1).toString()] = { cells: {} }
          if (row.height) {
            sheetData.rows[(rowIndex - 1).toString()].height = row.height * 1.5;
          }
          row.eachCell({ includeEmpty: true }, function (cell, colNumber) {
            let cellText = ''
            if (cell.value && cell.value.result) {
              // Excel 单元格有公式
              cellText = cell.value.result
            } else if (cell.value && cell.value.richText) {
              // Excel 单元格是多行文本
              for (let text in cell.value.richText) {
                // 多行文本做累加
                cellText += cell.value.richText[text].text
              }
            }
            else {
              // Excel 单元格无公式
              cellText = cell.value
            }

            // 背景色
            let backGroundColor = null
            if (cell.style.fill && cell.style.fill.fgColor && cell.style.fill.fgColor.argb) {
              backGroundColor = argb2hex(cell.style.fill.fgColor.argb)
            }

            if (backGroundColor) {
              cell.style.bgcolor = backGroundColor
            }
            // 字体颜色
            let fontColor = null
            if (cell.style.font && cell.style.font.color && cell.style.font.color.argb) {
              fontColor = argb2hex(cell.style.font.color.argb)
            }
            if (fontColor) {
              //console.log(fontColor)
              cell.style.color = fontColor
            }

            // 对齐 
            if (cell.style.alignment && cell.style.alignment.horizontal) {
              cell.style.align = cell.style.alignment.horizontal
              cell.style.valign = cell.style.alignment.vertical || "middle";
            }
            if (cell.style.border) {
              // excel border
              // {
              //   "left": {
              //       "style": "thin",
              //       "color": {
              //           "argb": "FF000000"
              //       }
              //   },
              //   "right": {
              //       "style": "thin",
              //       "color": {
              //           "argb": "FF000000"
              //       }
              //   },
              //   "top": {
              //       "style": "thin",
              //       "color": {
              //           "argb": "FF000000"
              //       }
              //   },
              //   "bottom": {
              //       "style": "thin",
              //       "color": {
              //           "argb": "FF000000"
              //       }
              //   }
              // }

              // xs border
              // {
              //   "bottom": [
              //       "thin",
              //       "#000"
              //   ],
              //   "top": [
              //       "thin",
              //       "#000"
              //   ],
              //   "left": [
              //       "thin",
              //       "#000"
              //   ],
              //   "right": [
              //       "thin",
              //       "#000"
              //   ]
              // }
              const { left, right, top, bottom } = cell.style.border;
              let bd = {};
              if (top) {
                bd.top = _.isArray(top) ? top : [top.style||"thin", argb2hex(top.color?.argb||"FF000000")];
              }
              if (bottom) {
                bd.bottom = _.isArray(bottom) ? bottom : [bottom.style||"thin", argb2hex(bottom.color?.argb||"FF000000")];
              }
              if (left) {
                bd.left = _.isArray(left) ? left : [left.style||"thin", argb2hex(left.color?.argb||"FF000000")];
              }
              if (right) {
                bd.right = _.isArray(right) ? right : [right.style||"thin", argb2hex(right.color?.argb||"FF000000")];
              }

              cell.style.border = bd;
            }
            //处理合并单元格
            let mergeAddress = _.find(mergeAddressData, function (o) { return o.startAddress == cell._address })
            if (mergeAddress) {
              // 遍历的单元格属于合并单元格
              if (cell.master.address != mergeAddress.startAddress) {
                // 不是合并单元格中的第一个单元格不需要计入数据源
                return
              }
              // 说明是合并单元格区域的起始单元格
              sheetData.rows[(rowIndex - 1).toString()].cells[(colNumber - 1).toString()] = { text: cellText, style: 0, merge: [mergeAddress.YRange, mergeAddress.XRange] }
              sheetData.styles.push(cell.style)
              //对应的style存放序号
              sheetData.rows[(rowIndex - 1).toString()].cells[(colNumber - 1).toString()].style = sheetData.styles.length - 1
            }
            else {
              // 非合并单元格
              sheetData.rows[(rowIndex - 1).toString()].cells[(colNumber - 1).toString()] = { text: cellText, style: 0 }
              //解析单元格,包含样式
              sheetData.styles.push(cell.style)
              //对应的style存放序号
              sheetData.rows[(rowIndex - 1).toString()].cells[(colNumber - 1).toString()].style = sheetData.styles.length - 1
            }
          });
        })
        workbookData.push(sheetData)
      })
      return workbookData;
    };

    var process_wb = (function () {
      var XPORT = document.getElementById('xport');
      return function process_wb(wb) {
        /* 转换exceljs数据为x-spreadsheet数据格式 */
        var data = e2x(wb);
        /* update x-spreadsheet */
        xspr.loadData(data);
        XPORT.disabled = false;
        if (typeof console !== 'undefined') console.log("output", new Date());
      };
    })();



    var do_file = (function () {
      return function do_file(files) {
        var f = files[0];
        var reader = new FileReader();
        reader.onload = function (e) {
          if (typeof console !== 'undefined') console.log("onload", new Date());
          var data = new Uint8Array(e.target?.result);
          const wb = new ExcelJS.Workbook();
          wb.xlsx.load(data).then(() => {
            process_wb(wb);
          })
        };
        reader.readAsArrayBuffer(f);
      };
    })();

    (function () {
      var drop = document.getElementById('drop');
      if (!drop.addEventListener) return;

      function handleDrop(e) {
        e.stopPropagation();
        e.preventDefault();
        do_file(e.dataTransfer.files);
      }

      function handleDragover(e) {
        e.stopPropagation();
        e.preventDefault();
        e.dataTransfer.dropEffect = 'copy';
      }

      drop.addEventListener('dragenter', handleDragover, false);
      drop.addEventListener('dragover', handleDragover, false);
      drop.addEventListener('drop', handleDrop, false);
    })();

    (function () {
      var xlf = document.getElementById('xlf');
      if (!xlf.addEventListener) return;
      function handleFile(e) { do_file(e.target.files); }
      xlf.addEventListener('change', handleFile, false);
    })();


    function export_xlsx() {
      var xsData = xspr.getData();
      const exceljsWorkbook = new ExcelJS.Workbook();
      exceljsWorkbook.modified = new Date();
      xsData.forEach(function (xws) {
        let rowobj = xws.rows;
        // 构造exceljs文档结构
        const exceljsSheet = exceljsWorkbook.addWorksheet(xws.name)
        // 读取列宽
        let sheetColumns = []
        let colIndex = 0
        for (let col in xws.cols) {
          if (xws.cols[col].width) {
            sheetColumns.push({ header: colIndex + '', key: colIndex + '', width: xws.cols[col].width / 8 })
          }
          colIndex++
        }
        exceljsSheet.columns = sheetColumns
        for (let ri = 0; ri < rowobj.len; ++ri) {
          let row = rowobj[ri];
          if (!row) continue;
          const exceljsRow = exceljsSheet.getRow(ri + 1)
          Object.keys(row.cells).forEach(function (k) {
            let idx = +k;
            if (isNaN(idx)) return;
            const exceljsCell = exceljsRow.getCell(Number(k) + 1)
            exceljsCell.value = row.cells[k].text
            // 对齐方式
            if (xws.styles[row.cells[k].style] && xws.styles[row.cells[k].style].alignment) {
              if (xws.styles[row.cells[k].style].alignment.vertical) {
                if (!exceljsCell.alignment) {
                  exceljsCell.alignment = {}
                }
                exceljsCell.alignment.vertical = xws.styles[row.cells[k].style].alignment.vertical
              }
              if (xws.styles[row.cells[k].style].alignment.horizontal) {
                if (!exceljsCell.alignment) {
                  exceljsCell.alignment = {}
                }
                exceljsCell.alignment.horizontal = xws.styles[row.cells[k].style].alignment.horizontal
              }
            }

            // 边框
            if (xws.styles[row.cells[k].style].border) {
              exceljsCell.border = {};
              const { left, right, top, bottom } = xws.styles[row.cells[k].style].border;
              if (left && left.length === 2) {
                exceljsCell.border.left = { style: left[0], color: { argb: hex2argb(left[1]) } }
              }
              if (right && right.length === 2) {
                exceljsCell.border.right = { style: right[0], color: { argb: hex2argb(right[1]) } }
              }
              if (top && top.length === 2) {
                exceljsCell.border.top = { style: top[0], color: { argb: hex2argb(top[1]) } }
              }
              if (bottom && bottom.length === 2) {
                exceljsCell.border.bottom = { style: bottom[0], color: { argb: hex2argb(bottom[1]) } }
              }
            }
            // 背景色
            if (xws.styles[row.cells[k].style].bgcolor) {
              var _bgColor = hex2argb(xws.styles[row.cells[k].style].bgcolor)
              // 设置exceljs背景色
              exceljsCell.fill = {
                type: 'pattern',
                pattern: 'solid',
                fgColor: { argb: _bgColor }
              }
            }
            // 字体
            exceljsCell.font = xws.styles[row.cells[k].style].font
            // 字体颜色
            if (xws.styles[row.cells[k].style].color) {
              exceljsCell.font.color = { argb: hex2argb(xws.styles[row.cells[k].style].color) }
            }
            // 合并单元格
            if (row.cells[k].merge) {
              // 开始行
              let startRow = ri + 1
              // 结束行,加上Y轴跨度
              let endRow = startRow + row.cells[k].merge[0]
              // 开始列
              let startColumn = Number(k) + 1
              // 结束列,加上X轴跨度
              let endColumn = startColumn + row.cells[k].merge[1]
              // 按开始行，开始列，结束行，结束列合并
              exceljsSheet.mergeCells(startRow, startColumn, endRow, endColumn);
            }
          });
        }
      });
      // writeBuffer 把写好的excel 转换成 ArrayBuffer 类型
      exceljsWorkbook.xlsx.writeBuffer().then((data) => {
        const link = document.createElement('a');
        // Blob 实现下载excel
        const blob = new Blob([data], {
          type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=UTF-8',
        });
        link.href = window.URL.createObjectURL(blob);
        link.download = 'export_xlsx.xlsx';
        link.click();
      });
    }
  </script>
  <script type="text/javascript">
    /* eslint no-use-before-define:0 */
    var _gaq = _gaq || [];
    _gaq.push(['_setAccount', 'UA-36810333-1']);
    _gaq.push(['_trackPageview']);

    (function () {
      var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
      ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
      var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s);
    })();
  </script>
  <!-- Cloudflare Pages Analytics -->
  <script defer src='https://static.cloudflareinsights.com/beacon.min.js'
    data-cf-beacon='{"token": "5045fe4c2b784ddb8c3c6ee7fa0593e5"}'></script><!-- Cloudflare Pages Analytics -->
</body>

</html>